package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.xmy.cultivate.entity.AuthAdminContent;
import com.xmy.cultivate.entity.LackCourseLog;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.entity.response.LackRecourseRate;
import com.xmy.cultivate.entity.views.LeadDataRs;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 缺课记录 Mapper 接口
 * </p>
 *
 * @author hpiggy
 * @since 2023-07-28
 */
public interface LackCourseLogMapper extends BaseMapper<LackCourseLog> {

    @Select({"<script>",
            "select *,(studentAllCount*15) as all_course_count from(",
            "select ",
            "id,school_id,teacher_id,subjects_id,grade_id,year_part,`quarter_num`,",
            "getStaffNameForId ( teacher_id ) AS teacherName,",
            "getSchoolName(school_id) as schoolName,",
            "count( id ) AS studentAllCount,",
            "sum(lack_count) as lack_course_count,",
            "sum( new_stu_lack_count ) AS new_stu_lack_count,",
            "sum( all_repair_count ) AS all_repair_count,",
            "sum( repair_count ) AS repair_count,",
            "grade_name,",
            "subjects_name",
            "from (",
            "select t1.*,",
            "SUM( CASE WHEN t2.is_repair &lt;&gt; 1 THEN 1 ELSE 0 END ) AS lack_count,",
            "SUM( CASE WHEN t2.is_repair &lt;&gt; 1 and t2.lack_type=2 THEN 1 ELSE 0 END ) AS new_stu_lack_count,",
            "SUM( CASE WHEN t3.is_sign_in =1 THEN 1 ELSE 0 END ) AS all_repair_count,",
            "SUM( CASE WHEN t3.is_sign_in =1 and t3.school_date BETWEEN #{startDate} and #{endDate} THEN 1 ELSE 0 END ) AS repair_count",
            "from",
            "student_grade_view as t1",
            "LEFT JOIN lack_and_record_view as t2 on t2.school_id = t1.school_id",
            "AND t2.subjects_id = t1.subjects_id",
            "AND t2.student_id = t1.student_id",
            "AND t2.year_part = t1.year_part",
            "AND t2.quarter_num = t1.quarter_num",
            "AND t2.lesson_type = t1.lesson_type",
            "LEFT JOIN repair_course as t3 on t2.id = t3.record_course_id and t3.is_sign_in =1 and t3.deleted =0",
            "where",
            "t1.year_part = ${yearPart}",
            "and t1.quarter_num = ${quarterNum}",
            "and t1.`status`&lt;&gt;3",
            "and t1.is_last = 1",
            "and t1.is_makeup = 0",
            "and t1.deleted = 0",
            "and t1.`status` = 1",
            "and t1.reading_status = 1",
            "and t1.lesson_type=${lessonType}",
            "and t1.year_class_id not in(10,11,12)",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
            "  and t1.school_id in(${schoolId})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "  and t1.subjects_id = ${subjectsId}",
            "</when>",
            "<when test='trackId!=null and trackId!=&apos;&apos; and type ==1'>",
            "  and t1.teacher_id in(select sourse_id from track_detail where deleted = 0 and track_id =#{trackId})",
            "</when>",
            "<when test='trackId!=null and trackId!=&apos;&apos; and type ==4'>",
            "  and t1.school_id in(select sourse_id from track_detail where deleted = 0 and track_id =#{trackId})",
            "</when>",
            "<when test='type ==3'>",
            "  and t1.teacher_id in(select admin_id from role_admin where role_id = 1568160887369994242)",
            "</when>",
            "GROUP BY",
            "school_id,",
            "student_id,",
            "subjects_id,",
            "teacher_id",
            ") as t2",
            "<when test='type == 1 or type == 3'>",
            "GROUP BY teacher_id",
            "</when>",
            "<when test='type == 2'>",
            "GROUP BY school_id,teacher_id",
            "</when>",
            "<when test='type == 4'>",
            "GROUP BY school_id",
            "</when>",
            ")as t3",
            "</script>"})
    public List<LackRecourseRate> getLackRade(@Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("schoolId") String schoolId,@Param("subjectsId") Long subjectsId, @Param("type") Integer type, @Param("startDate") String startDate, @Param("endDate") String endDate, @Param("trackId") Long trackId,@Param("lessonType") String lessonType);

    @Select({"<script>",
            "SELECT",
            "*",
            "FROM",
            "(",
            "select ",
            "id,school_id,teacher_id,subjects_id,grade_id,year_part,`quarter_num`,",
            "<when test='detailType==1'>",
            "sum(lack_count) as lack_course_count,",
            "sum( all_repair_count ) AS all_repair_count,",
            "sum( repair_count ) AS repair_count,",
            "</when>",
            "getStaffNameForId ( teacher_id ) AS teacherName,",
            "getSchoolName(school_id) as schoolName,",
            "grade_name,",
            "subjects_name,",
            "student_name,",
            "is_sign_in,",
            "is_repair,",
            "school_date,",
            "time_start_b as time_start,",
            "time_end_b as time_end,",
            "CASE course_type WHEN 3 THEN '请假补课' WHEN 4 THEN '调课' WHEN 5 THEN '消课' WHEN 6 THEN '缺课补课' END as course_type_name",
            "from (",
            "select t1.*,",
            "t2.is_repair,",
            "<when test='detailType==1'>",
            "SUM( CASE WHEN t2.is_repair &lt;&gt; 1 THEN 1 ELSE 0 END ) AS lack_count,",
            "SUM( CASE WHEN t3.is_sign_in = 1 THEN 1 ELSE 0 END ) AS all_repair_count,",
            "SUM( CASE WHEN t3.is_sign_in = 1 AND t3.school_date BETWEEN null AND null THEN 1 ELSE 0 END ) AS repair_count,",
            "</when>",
            "t3.is_sign_in,",
            "t3.course_type,",
            "t3.school_date,",
            "t3.time_start as time_start_b,",
            "t3.time_end as time_end_b",
            "from",
            "student_grade_view as t1",
            "LEFT JOIN lack_and_record_view AS t2 ON t2.school_id = t1.school_id ",
            "AND t2.subjects_id = t1.subjects_id ",
            "AND t2.student_id = t1.student_id ",
            "AND t2.year_part = t1.year_part ",
            "AND t2.quarter_num = t1.quarter_num ",
            "AND t2.lesson_type = t1.lesson_type",
            "LEFT JOIN repair_course AS t3 ON t2.id = t3.record_course_id ",
            "AND t3.is_sign_in = 1 ",
            "AND t3.deleted = 0 ",
            "where",
            "t1.year_part = ${yearPart}",
            "and t1.quarter_num = ${quarterNum}",
            "and t1.`status`&lt;&gt;3 ",
            "and t1.is_last = 1",
            "and t1.is_makeup = 0",
            "and t1.deleted = 0",
            "and t1.`status` = 1",
            "and t1.reading_status = 1",
            "and t1.lesson_type=${lessonType}",
            "and t1.year_class_id not in(10,11,12)",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "  and t1.subjects_id = #{subjectsId}",
            "</when>",
            "<when test='type == 1 or type == 3'>",
            "and t1.teacher_id=#{teacherId}",
            "</when>",
            "<when test='type == 2'>",
            "and t1.teacher_id=#{teacherId} and t1.school_id = #{schoolId}",
            "</when>",
            "<when test='type == 4'>",
            "and t1.school_id=#{schoolId}",
            "</when>",
            "<when test='gradeId!=null and gradeId!=&apos;&apos;'>",
            "and t1.grade_id=#{gradeId}",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and t1.subjects_id=#{subjectsId}",
            "</when>",

            "<when test='detailType==1'>",
            "and t2.is_repair &lt;&gt; 1 and is_repair is not null",
            "</when>",
            "<when test='detailType==2'>",
            "and t3.is_sign_in =1",
            "</when>",
            "<when test='stuType==2'>",
            "and t2.lack_type =2",
            "</when>",
            "<when test='detailType==2 and startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos;'>",
            "and t3.school_date BETWEEN #{startDate} and #{endDate}",
            "</when>",
            "<when test='detailType==1'>",
            "GROUP BY",
            "t1.school_id,",
            "t1.student_id,",
            "t1.subjects_id,",
            "t1.teacher_id",
            "</when>",
            ") as t2",
            "<when test='detailType==1'>",
            "GROUP BY student_id,subjects_id",
            "ORDER BY lack_course_count desc",
            "</when>",
            ") AS t",
            "WHERE 1=1 ",
            "</script>"})
    public IPage<LackRecourseRate> getLackRadeDetail(IPage<?> page, @Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("schoolId") Long schoolId, @Param("teacherId") Long teacherId, @Param("gradeId") Long gradeId, @Param("subjectsId") Long subjectsId, @Param("type") Integer type, @Param("startDate") String startDate, @Param("endDate") String endDate, @Param("trackId") Long trackId, @Param("detailType") Integer detailType, @Param("lessonType") String lessonType, @Param("isLack") String isLack,@Param("stuType") String stuType);


    @Select({"<script>",
            "SELECT",
            "<if test= 'type == 6 '>",
            "t02.*,",
            "t01.pid AS schoolPid,",
            "SUM(litter_lack_course_count) as lack_course_count,",
            "SUM( CASE WHEN t02.id IS NOT NULL ",
            // ---权限数据开始，跟自己所带的年级、校区、科目包起来---
            "AND ((",
            "t02.year_class_id in(${authAdminContent.yearClassIds})",
            "and t02.subjects_id in(${authAdminContent.subjectsIds})",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "and t02.year_class_id in (${yearClassId})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "and t02.subjects_id in (${subjectsId}) ",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
            "and t02.school_id in(${schoolId})",
            "</when>",
            ") OR (",
            "t02.teacher_id = ${leadDataRs.teacherId} AND t02.school_id IN ( ${leadDataRs.schoolIds} ) AND t02.year_class_id IN ( ${leadDataRs.yearClassIds} ) AND t02.subjects_id IN ( ${leadDataRs.subjectsIds} ))) ",
            // ---权限数据结束---
            "<when test='classTypeIdStr!=null and classTypeIdStr!=&apos;&apos; '>",
            "and t02.course_type_id in (${classTypeIdStr})",
            "</when>",
            "<when test='startLessonNum!=null and startLessonNum!=&apos;&apos; and endLessonNum!=null and endLessonNum!=&apos;&apos;'>",
            "AND lesson_num BETWEEN ${startLessonNum} AND ${endLessonNum} ",
            "</when>",
            "THEN 1 ELSE 0 END ) AS student_all_count,",

            "SUM(litter_all_repair) as all_repair_count,",
            "SUM(litter_date_repair) as repair_count",
            "FROM",
            "( SELECT * FROM school_quarter WHERE year_part = ${yearPart} AND quarter_num = ${quarterNum} AND deleted = 0) AS t01",
            "LEFT JOIN ( ",
            "</if>",
            "<if test= 'type != 6 '>",
            "t02.*,",
            "sum(litter_lack_course_count) as lack_course_count,",
            "count(id) as student_all_count,",
            "SUM(litter_all_repair) as all_repair_count,",
            "SUM(litter_date_repair) as repair_count",
            "FROM (",
            "</if>",
            "SELECT",
            "*,",
            "<if test= 'type == 6 '>",
            "SUM( CASE WHEN id IS NOT NULL ",
            // ---权限数据开始，跟自己所带的年级、校区、科目包起来---
            "AND ((",
            "year_class_id in(${authAdminContent.yearClassIds})",
            "and subjects_id in(${authAdminContent.subjectsIds})",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "and year_class_id in (${yearClassId})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "and subjects_id in (${subjectsId}) ",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
            "and school_id in(${schoolId})",
            "</when>",
            ") OR (",
            "teacher_id = ${leadDataRs.teacherId} AND school_id IN ( ${leadDataRs.schoolIds} ) AND year_class_id IN ( ${leadDataRs.yearClassIds} ) AND subjects_id IN ( ${leadDataRs.subjectsIds} ))) ",
            // ---权限数据结束---
            "<when test='classTypeIdStr!=null and classTypeIdStr!=&apos;&apos; '>",
            "and course_type_id in (${classTypeIdStr})",
            "</when>",
            "THEN 1 ELSE 0 END ) AS litter_lack_course_count,",

            "SUM( CASE WHEN `is_repair` IN ( 1 ) ",
            "<when test='lackType == 0'>",
//                "and repair_end_date_time &lt;='2025-05-05 18:00:00' ",
            "</when>",
            // ---权限数据开始，跟自己所带的年级、校区、科目包起来---
            "AND ((",
            "year_class_id in(${authAdminContent.yearClassIds})",
            "and subjects_id in(${authAdminContent.subjectsIds})",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "and year_class_id in (${yearClassId})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "and subjects_id in (${subjectsId}) ",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
            "and school_id in(${schoolId})",
            "</when>",
            ") OR (",
            "teacher_id = ${leadDataRs.teacherId} AND school_id IN ( ${leadDataRs.schoolIds} ) AND year_class_id IN ( ${leadDataRs.yearClassIds} ) AND subjects_id IN ( ${leadDataRs.subjectsIds} ))) ",
            // ---权限数据结束---
            "<when test='classTypeIdStr!=null and classTypeIdStr!=&apos;&apos; '>",
            "and course_type_id in (${classTypeIdStr})",
            "</when>",
            "THEN 1 ELSE 0 END ) AS litter_all_repair,",

            "SUM( CASE WHEN `is_repair` IN ( 1 ) AND school_date BETWEEN #{startDate} AND #{endDate} ",
            "<when test='lackType == 0'>",
//                "and repair_end_date_time &lt;='2025-05-05 18:00:00' ",
            "</when>",
            // ---权限数据开始，跟自己所带的年级、校区、科目包起来---
            "AND ((",
            "year_class_id in(${authAdminContent.yearClassIds})",
            "and subjects_id in(${authAdminContent.subjectsIds})",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                "and year_class_id in (${yearClassId})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                "and subjects_id in (${subjectsId}) ",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                "and school_id in(${schoolId})",
            "</when>",
            ") OR (",
            "teacher_id = ${leadDataRs.teacherId} AND school_id IN ( ${leadDataRs.schoolIds} ) AND year_class_id IN ( ${leadDataRs.yearClassIds} ) AND subjects_id IN ( ${leadDataRs.subjectsIds} ))) ",
            // ---权限数据结束---
            "<when test='classTypeIdStr!=null and classTypeIdStr!=&apos;&apos; '>",
            "and course_type_id in (${classTypeIdStr})",
            "</when>",
            "THEN 1 ELSE 0 END ) AS litter_date_repair ",
            "</if>",
            "<if test= 'type != 6 '>",

            "count( id ) AS litter_lack_course_count,",
            "SUM( CASE WHEN `is_repair` IN ( 1 ) ",
            "<when test='lackType == 0'>",
//            "and repair_end_date_time &lt;='2025-05-05 18:00:00' ",
            "</when>",
            "THEN 1 ELSE 0 END ) AS litter_all_repair,",

            "SUM( CASE WHEN `is_repair` IN ( 1 ) ",
            "<when test='lackType == 0'>",
//            "and repair_end_date_time &lt;='2025-05-05 18:00:00' ",
            "</when>",
            "AND school_date BETWEEN #{startDate} AND #{endDate} THEN 1 ELSE 0 END ) AS litter_date_repair ",
            "</if>",
            "FROM",
            "(",
            "SELECT",
            "t1.id,",
            "t1.lesson_num,",
            "t1.grade_id,",
            "t1.school_id,",
            "t1.student_id,",
            "getSchoolName ( t1.school_id ) AS school_name,",
            "t2.grade_name,",
            "t2.teacher_id,",
            "t2.teacher_name,",
            "t2.year_class_id,",
            "t2.course_type_id,",
            "t4.`status`,",
            "t4.school_date, ",
            "t1.is_repair, ",
            "t1.repair_end_date_time, ",
            "t1.subjects_id ",
            "FROM",
            "( SELECT * FROM lack_course_log",
            "WHERE year_part = ${yearPart} AND quarter_num = ${quarterNum} and lesson_type = ${lessonType} AND deleted = 0 and lack_type = ${lackType} and status in (0,1)",
            ") AS t1",
            "LEFT JOIN (",
            "SELECT",
            "t001.id,",
            "t001.NAME AS grade_name,",
            "t001.year_class_id,",
            "t001.course_type_id,",
            "t002.id AS teacher_id,",
            "t002.NAME AS teacher_name ",
            "FROM",
            "grade AS t001",
            "LEFT JOIN staff AS t002 ON t001.teacher_id = t002.id ",
            ") AS t2 ON t1.grade_id = t2.id",
            "LEFT JOIN ( SELECT * FROM repair_course WHERE year_part = ${yearPart} AND `quarter` = ${quarterNum} ) AS t3 ON t1.id = t3.record_course_id",
            "LEFT JOIN ( SELECT * FROM record_course WHERE year_part = ${yearPart} AND `quarter` = ${quarterNum} ) AS t4 ON t3.id = t4.scheduling_detail_id ",
            ") AS t001 ",
            "where 1=1 ",
            "<when test='type ==3'>",
            "and teacher_id in(select admin_id from role_admin where role_id = 1568160887369994242)",
            "</when>",
            "<if test= 'type != 6 '>",

            // ---权限数据开始，跟自己所带的年级、校区、科目包起来---
            "AND ((",
            "year_class_id in(${authAdminContent.yearClassIds})",
            "and subjects_id in(${authAdminContent.subjectsIds})",
            "<when test='authAdminContent.isPerson==1'>",
                "and teacher_id = ${authAdminContent.teacherId} ",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                "and year_class_id in (${yearClassId})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                "and subjects_id in (${subjectsId}) ",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                "and school_id in(${schoolId})",
            "</when>",
            ") OR (",
            "teacher_id = ${leadDataRs.teacherId} AND school_id IN ( ${leadDataRs.schoolIds} ) AND year_class_id IN ( ${leadDataRs.yearClassIds} ) AND subjects_id IN ( ${leadDataRs.subjectsIds} ))) ",
            // ---权限数据结束---

            "<when test='classTypeIdStr!=null and classTypeIdStr!=&apos;&apos; '>",
            "and course_type_id in (${classTypeIdStr})",
            "</when>",
            "<when test='startLessonNum!=null and startLessonNum!=&apos;&apos; and endLessonNum!=null and endLessonNum!=&apos;&apos;'>",
            "AND lesson_num BETWEEN ${startLessonNum} AND ${endLessonNum} ",
            "</when>",
            "</if>",
            "GROUP BY",
            "school_id,",
            "teacher_id,",
            "student_id ",
            "<if test= 'type == 6 '>",
            ") AS t02 ON t01.school_id = t02.school_id ",
            "</if>",
            "<if test= 'type != 6 '>",
            ") AS t02 ",
            "</if>",
            "<when test='type == 1 or type == 3'>",
            "GROUP BY t02.teacher_id",
            "</when>",
            "<when test='type == 2'>",
            "GROUP BY t02.school_id,t02.teacher_id",
            "</when>",
            "<when test='type == 4 or type == 6 '>",
            "GROUP BY t02.school_id",
            "</when>",
            "HAVING t02.id IS NOT NULL",
            "</script>"})
    public List<LackRecourseRate> getRepairLackRade(@Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("schoolId") String schoolId, @Param("subjectsId") String subjectsId, @Param("type") Integer type, @Param("startDate") String startDate, @Param("endDate") String endDate, @Param("trackId") Long trackId, @Param("lessonType") String lessonType, @Param("yearClassId") String yearClassId, @Param("classTypeIdStr") String classTypeIdStr, @Param("startLessonNum") String startLessonNum, @Param("endLessonNum") String endLessonNum, @Param("lackType") String lackType, @Param("authAdminContent") AuthAdminContent authAdminContent,  @Param("leadDataRs") LeadDataRs leadDataRs);

    @Select({"<script>",
            "SELECT",
            "t1.id,",
            "<when test='isCollect == 0'>",
            "t1.lesson_num,",
            "</when>",
            "<when test='isCollect == 1'>",
            "GROUP_CONCAT(t1.lesson_num ORDER BY t1.lesson_num SEPARATOR ',') AS lesson_num,",
            "</when>",
            "t1.school_id,",
            "t1.student_id,",
            "t2.teacher_id,",
            "t2.year_class_id,",
            "t2.course_type_id,",
            "t4.`status`,",
            "t5.`name` AS studentName,",
            "t4.time_start,",
            "t4.time_end,",
            "t4.school_date, ",
            "t1.is_repair, ",
            "t1.repair_end_date_time, ",
            "t1.subjects_id, ",
            "t2.grade_name,",
            "getStaffNameForId(t2.teacher_id) AS teacherName",
            "FROM",
            "( SELECT * FROM lack_course_log",
            "WHERE year_part = ${yearPart} AND quarter_num = ${quarterNum} and lesson_type = ${lessonType} AND deleted = 0 and lack_type = ${lackType} and status in (0,1)",
            "<when test='lessonNum!=null and lessonNum!=&apos;&apos; '>",
            "and lesson_num IN (${lessonNum}) ",
            "</when>",
            ") AS t1",
            "LEFT JOIN (",
            "SELECT",
            "t001.id,",
            "t001.NAME AS grade_name,",
            "t001.year_class_id,",
            "t001.course_type_id,",
            "t001.teacher_id ",
            "FROM",
            "grade AS t001",
            ") AS t2 ON t1.grade_id = t2.id",
            "LEFT JOIN ( SELECT * FROM repair_course WHERE year_part = ${yearPart} AND `quarter` = ${quarterNum} ) AS t3 ON t1.id = t3.record_course_id",
            "LEFT JOIN ( SELECT * FROM record_course WHERE year_part = ${yearPart} AND `quarter` = ${quarterNum} ) AS t4 ON t3.id = t4.scheduling_detail_id ",
            "LEFT JOIN student AS t5 ON t5.id = t1.student_id ",
            "where 1=1",
            "<when test='type ==3'>",
            "and t2.teacher_id in(select admin_id from role_admin where role_id = 1568160887369994242)",
            "</when>",

            // ---权限数据开始，跟自己所带的年级、校区、科目包起来---
            "and ((",
            "t1.subjects_id in(${authAdminContent.subjectsIds})",
            "and t2.year_class_id in(${authAdminContent.yearClassIds})",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                "and t1.subjects_id IN (${subjectsId}) ",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                "and t1.school_id in(${schoolId})",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                "and t2.year_class_id in (${yearClassId})",
            "</when>",
            "<when test='teacherId!=null and teacherId!=&apos;&apos; '>",
                "and t2.teacher_id = ${teacherId}",
            "</when>",
            ") or (t2.teacher_id = ${leadDataRs.teacherId} AND t1.school_id IN ( ${leadDataRs.schoolIds} ) AND t2.year_class_id IN ( ${leadDataRs.yearClassIds} ) AND t1.subjects_id IN ( ${leadDataRs.subjectsIds} ) ))",
            // ---权限数据结束---

            "<when test='classTypeIdStr!=null and classTypeIdStr!=&apos;&apos; '>",
            "and course_type_id in (${classTypeIdStr})",
            "</when>",
            "<when test='gradeId!=null and gradeId!=&apos;&apos; '>",
            "and t2.id = ${gradeId}",
            "</when>",
            "<when test='startLessonNum!=null and startLessonNum!=&apos;&apos; and endLessonNum!=null and endLessonNum!=&apos;&apos;'>",
            "AND t1.lesson_num BETWEEN ${startLessonNum} AND ${endLessonNum} ",
            "</when>",
            "<when test='isRepair!=null and isRepair!=&apos;&apos; '>",
                "<when test='isRepair == 2'>",
                " and t1.is_repair = 1",
                    "<when test='lackType == 0'>",
                        "and t1.repair_end_date_time &lt;='2025-05-05 18:00:00' ",
                    "</when>",
                "</when>",
                "<when test='isRepair == 3'>",
                    " and t1.is_repair &lt;&gt; 1",
                "</when>",
                "<when test='isRepair == 4'>",
                    " and t1.is_repair = 1 AND t4.school_date BETWEEN '${startDate}' AND '${endDate}' ",
                    "<when test='lackType == 0'>",
                        "and t1.repair_end_date_time &lt;='2025-05-05 18:00:00' ",
                    "</when>",
                "</when>",
            "</when>",
            "<when test='isCollect == 1'>",
            "GROUP BY t1.student_id,t2.teacher_id",
            "</when>",
            "</script>"})
    public IPage<LackRecourseRate> getRepairLackRadeDetail(IPage<?> page, @Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("schoolId") String schoolId, @Param("teacherId") Long teacherId, @Param("gradeId") Long gradeId, @Param("subjectsId") String subjectsId, @Param("type") Integer type, @Param("startDate") String startDate, @Param("endDate") String endDate, @Param("trackId") Long trackId, @Param("lessonType") String lessonType, @Param("yearClassId") String yearClassId, @Param("isRepair") String isRepair, @Param("classTypeIdStr") String classTypeIdStr, @Param("isCollect") Integer isCollect, @Param("lessonNum") String lessonNum, @Param("startLessonNum") String startLessonNum, @Param("endLessonNum") String endLessonNum, @Param("lackType") String lackType, @Param("authAdminContent") AuthAdminContent authAdminContent, @Param("leadDataRs") LeadDataRs leadDataRs);

    @Select({"<script>",
            "SELECT *,",
            "getStaffNameForId(teacher_id) AS teacher_name,",
            "IFNULL( sum( litter_lack_course_count ), 0 ) AS lack_course_count,",
            "IFNULL( SUM(CASE WHEN id IS NOT NULL ",
            // ---权限数据开始，跟自己所带的年级、校区、科目包起来---
            "AND ((",
            "subjects_id in(${authAdminContent.subjectsIds})",
            "and year_class_id in(${authAdminContent.yearClassIds})",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "and year_class_id in (${yearClassId})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "and subjects_id in(${subjectsId})",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
            "and school_id in(${schoolId})",
            "</when>",
            ") OR (",
            "teacher_id = ${leadDataRs.teacherId} AND school_id IN ( ${leadDataRs.schoolIds} ) AND year_class_id IN ( ${leadDataRs.yearClassIds} ) AND subjects_id IN ( ${leadDataRs.subjectsIds} ))) ",
            // ---权限数据结束---
            "<when test='classTypeIdStr!=null and classTypeIdStr!=&apos;&apos; '>",
            "and course_type_id in (${classTypeIdStr})",
            "</when>",
            "THEN 1 ELSE 0 END ), 0 ) AS student_all_count,",
            "IFNULL( SUM( litter_all_repair ), 0 ) AS all_repair_count,",
            "IFNULL( SUM( litter_date_repair ), 0 ) AS repair_count ",
            "FROM (",
            "SELECT ",
            "team_id,team_name,team_pid,",
            "t01.sourse_id AS teacher_id,",
            "t02.lesson_num,",
            "t02.year_class_id,",
            "t02.course_type_id,",
            "t02.`status`,",
            "t02.school_date,",
            "t02.school_id,",
            "t02.is_repair,",
            "subjects_id,",
            "t02.litter_lack_course_count,",
            "t02.litter_all_repair,",
            "t02.litter_date_repair,",
            "t02.id ",
            "FROM (",
            "SELECT td.*,",
            "td.sourse_id AS teacher_id,",
            "tm.team_name,",
            "tm.id AS team_id,",
            "tm.pid AS team_pid ",
            "FROM ",
            "track_market AS tm ",
            "LEFT JOIN track_detail AS td ON tm.id = td.track_id AND tm.deleted = td.deleted ",
            "WHERE ",
            "tm.classify = ${classify} ",
            "AND tm.track_type = ${trackType}",
            "AND tm.`status` = 1 ",
            "AND tm.deleted = 0 ",
            "AND tm.year_part = ${yearPart} ",
            "AND tm.quarter_num = ${quarterNum} ",
            "<when test='teamId!=null and teamId!=&apos;&apos; '>",
            "AND tm.id in (${teamId})",
            "</when>",
            "GROUP BY tm.id,td.sourse_id ",
            ") AS t01 ",
            "LEFT JOIN (",
            "SELECT *,",

            "SUM( CASE WHEN id IS NOT NULL ",
            // ---权限数据开始，跟自己所带的年级、校区、科目包起来---
            "AND ((",
            "subjects_id in(${authAdminContent.subjectsIds})",
            "and year_class_id in(${authAdminContent.yearClassIds})",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "and year_class_id in (${yearClassId})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "and subjects_id in(${subjectsId})",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
            "and school_id in(${schoolId})",
            "</when>",
            ") OR (",
            "teacher_id = ${leadDataRs.teacherId} AND school_id IN ( ${leadDataRs.schoolIds} ) AND year_class_id IN ( ${leadDataRs.yearClassIds} ) AND subjects_id IN ( ${leadDataRs.subjectsIds} ))) ",
            // ---权限数据结束---
            "<when test='classTypeIdStr!=null and classTypeIdStr!=&apos;&apos; '>",
            "and course_type_id in (${classTypeIdStr})",
            "</when>",
            "<when test='startLessonNum!=null and startLessonNum!=&apos;&apos; and endLessonNum!=null and endLessonNum!=&apos;&apos;'>",
            "AND lesson_num BETWEEN ${startLessonNum} AND ${endLessonNum} ",
            "</when>",
            "THEN 1 ELSE 0 END ) AS litter_lack_course_count,",

            "SUM( CASE WHEN `is_repair` IN ( 1 ) ",
            "<when test='lackType == 0'>",
//                "and repair_end_date_time &lt;='2025-05-05 18:00:00' ",
            "</when>",
            // ---权限数据开始，跟自己所带的年级、校区、科目包起来---
            "AND ((",
            "subjects_id in(${authAdminContent.subjectsIds})",
            "and year_class_id in(${authAdminContent.yearClassIds})",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "and year_class_id in (${yearClassId})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "and subjects_id in(${subjectsId})",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
            "and school_id in(${schoolId})",
            "</when>",
            ") OR (",
            "teacher_id = ${leadDataRs.teacherId} AND school_id IN ( ${leadDataRs.schoolIds} ) AND year_class_id IN ( ${leadDataRs.yearClassIds} ) AND subjects_id IN ( ${leadDataRs.subjectsIds} ))) ",
            // ---权限数据结束---
            "<when test='classTypeIdStr!=null and classTypeIdStr!=&apos;&apos; '>",
            "and course_type_id in (${classTypeIdStr})",
            "</when>",
            "<when test='startLessonNum!=null and startLessonNum!=&apos;&apos; and endLessonNum!=null and endLessonNum!=&apos;&apos;'>",
            "AND lesson_num BETWEEN ${startLessonNum} AND ${endLessonNum} ",
            "</when>",
            "THEN 1 ELSE 0 END ) AS litter_all_repair,",

            "SUM( CASE WHEN `is_repair` IN ( 1 ) AND school_date BETWEEN #{startDate} AND #{endDate} ",
            "<when test='lackType == 0'>",
//                "and repair_end_date_time &lt;='2025-05-05 18:00:00' ",
            "</when>",
            // ---权限数据开始，跟自己所带的年级、校区、科目包起来---
            "AND ((",
            "subjects_id in(${authAdminContent.subjectsIds})",
            "and year_class_id in(${authAdminContent.yearClassIds})",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "and year_class_id in (${yearClassId})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "and subjects_id in(${subjectsId})",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
            "and school_id in(${schoolId})",
            "</when>",
            ") OR (",
            "teacher_id = ${leadDataRs.teacherId} AND school_id IN ( ${leadDataRs.schoolIds} ) AND year_class_id IN ( ${leadDataRs.yearClassIds} ) AND subjects_id IN ( ${leadDataRs.subjectsIds} ))) ",
            // ---权限数据结束---
            "<when test='classTypeIdStr!=null and classTypeIdStr!=&apos;&apos; '>",
            "and course_type_id in (${classTypeIdStr})",
            "</when>",
            "<when test='startLessonNum!=null and startLessonNum!=&apos;&apos; and endLessonNum!=null and endLessonNum!=&apos;&apos;'>",
            "AND lesson_num BETWEEN ${startLessonNum} AND ${endLessonNum} ",
            "</when>",
            "THEN 1 ELSE 0 END ) AS litter_date_repair ",
            "FROM",
            "(",
            "SELECT",
            "t1.id,",
            "t1.lesson_num,",
            "t1.grade_id,",
            "t1.school_id,",
            "t1.student_id,",
            "getSchoolName ( t1.school_id ) AS school_name,",
            "t2.grade_name,",
            "t2.teacher_id,",
            "t2.teacher_name,",
            "t2.year_class_id,",
            "t2.course_type_id,",
            "t4.`status`,",
            "t4.school_date, ",
            "t1.subjects_id,",
            "t1.is_repair, ",
            "t1.repair_end_date_time ",
            "FROM",
            "( SELECT * FROM lack_course_log WHERE year_part = ${yearPart} AND quarter_num = ${quarterNum} and lesson_type = 1 AND deleted = 0 and lack_type = ${lackType} and status in (0,1)) AS t1 ",
            "LEFT JOIN (",
            "SELECT",
            "t001.id,",
            "t001.NAME AS grade_name,",
            "t001.year_class_id,",
            "t001.course_type_id,",
            "t002.id AS teacher_id,",
            "t002.NAME AS teacher_name ",
            "FROM",
            "grade AS t001",
            "LEFT JOIN staff AS t002 ON t001.teacher_id = t002.id ",
            ") AS t2 ON t1.grade_id = t2.id",
            "LEFT JOIN ( SELECT * FROM repair_course WHERE year_part = ${yearPart} AND `quarter` = ${quarterNum} ) AS t3 ON t1.id = t3.record_course_id",
            "LEFT JOIN ( SELECT * FROM record_course WHERE year_part = ${yearPart} AND `quarter` = ${quarterNum} ) AS t4 ON t3.id = t4.scheduling_detail_id ",
            ") AS t001 ",
            "where 1=1 ",
            "GROUP BY school_id,teacher_id,student_id,subjects_id ",
            ") AS t02 ON t02.teacher_id = t01.teacher_id ",
            "AND ( find_in_set( t02.year_class_id, t01.year_class_id_str ) OR t01.year_class_id_str = '' ) ",
            "AND ( find_in_set( t02.school_id, t01.school_id_str ) OR t01.school_id_str = '' ) ",
            "AND ( find_in_set( t02.subjects_id, t01.subjects_id_str ) OR t01.subjects_id_str = '' ) ",
            "WHERE 1=1",
            ") AS t2 ",
            "GROUP BY team_id ",
            "<when test='teamId!=null and teamId!=&apos;&apos; '>",
            ",teacher_id",
            "</when>",
            "</script>"})
    public List<LackRecourseRate> getRepairLackRadeTeamList(@Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("subjectsId") String subjectsId, @Param("startDate") String startDate, @Param("endDate") String endDate, @Param("yearClassId") String yearClassId, @Param("classTypeIdStr") String classTypeIdStr, @Param("teamId") String teamId, @Param("classify") String classify, @Param("trackType") String trackType, @Param("startLessonNum") String startLessonNum, @Param("endLessonNum") String endLessonNum, @Param("lackType") String lackType, @Param("authAdminContent") AuthAdminContent authAdminContent, @Param("schoolId") String schoolId, @Param("leadDataRs") LeadDataRs leadDataRs);

    @Select({"<script>",
            "SELECT t02.* ",
            "FROM (",
            "SELECT ",
            "td.*,",
            "td.sourse_id AS teacher_id,",
            "tm.team_name,",
            "tm.id AS team_id,",
            "tm.pid AS team_pid ",
            "FROM ",
            "track_market AS tm",
            "LEFT JOIN track_detail AS td ON tm.id = td.track_id AND tm.deleted = td.deleted ",
            "WHERE ",
            "tm.classify = ${classify} ",
            "AND tm.track_type = ${trackType}",
            "AND tm.`status` = 1 ",
            "AND tm.deleted = 0 ",
            "AND tm.year_part = ${yearPart} ",
            "AND tm.quarter_num = ${quarterNum} ",
            "AND tm.id IN (${teamId}) ",
            "AND td.sourse_id = ${teacherId} ",
            "GROUP BY tm.id,td.sourse_id ",
            ") AS t01 ",
            "LEFT JOIN (",
            "SELECT",
            "t1.id,",
            "<when test='isCollect == 0'>",
            "t1.lesson_num,",
            "</when>",
            "<when test='isCollect == 1'>",
            "GROUP_CONCAT(t1.lesson_num ORDER BY t1.lesson_num SEPARATOR ',') AS lesson_num,",
            "</when>",
            "t1.grade_id,",
            "t1.school_id,",
            "t1.student_id,",
            "t1.subjects_id,",
            "getSchoolName ( t1.school_id ) AS school_name,",
            "t2.grade_name,",
            "t2.teacher_id,",
            "t2.teacher_name,",
            "t2.year_class_id,",
            "t2.course_type_id,",
            "t4.`status`,",
            "t5.`name` AS studentName,",
            "t4.time_start,",
            "t4.time_end,",
            "t4.school_date, ",
            "t1.is_repair, ",
            "t1.repair_end_date_time ",
            "FROM",
            "( SELECT * FROM lack_course_log",
            "WHERE year_part = ${yearPart} AND quarter_num = ${quarterNum} and lesson_type = 1 AND deleted = 0 and lack_type = ${lackType} and status in (0,1)",
            "<when test='lessonNum!=null and lessonNum!=&apos;&apos; '>",
            "and lesson_num IN (${lessonNum}) ",
            "</when>",
            ") AS t1",
            "LEFT JOIN (",
            "SELECT",
            "t001.id,",
            "t001.NAME AS grade_name,",
            "t001.year_class_id,",
            "t001.course_type_id,",
            "t002.id AS teacher_id,",
            "t002.NAME AS teacher_name ",
            "FROM",
            "grade AS t001",
            "LEFT JOIN staff AS t002 ON t001.teacher_id = t002.id ",
            ") AS t2 ON t1.grade_id = t2.id",
            "LEFT JOIN ( SELECT * FROM repair_course WHERE year_part = ${yearPart} AND `quarter` = ${quarterNum} ) AS t3 ON t1.id = t3.record_course_id",
            "LEFT JOIN ( SELECT * FROM record_course WHERE year_part = ${yearPart} AND `quarter` = ${quarterNum} ) AS t4 ON t3.id = t4.scheduling_detail_id ",
            "LEFT JOIN student AS t5 ON t5.id = t1.student_id ",
            "where 1=1",

            // ---权限数据开始，跟自己所带的年级、校区、科目包起来---
            "and ((",
            "t1.subjects_id in(${authAdminContent.subjectsIds})",
            "and t2.year_class_id in(${authAdminContent.yearClassIds})",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "and t1.subjects_id IN (${subjectsId}) ",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
            "and t1.school_id in(${schoolId})",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "and t2.year_class_id in (${yearClassId})",
            "</when>",
            "<when test='teacherId!=null and teacherId!=&apos;&apos; '>",
            "and t2.teacher_id = ${teacherId}",
            "</when>",
            ") or (t2.teacher_id = ${leadDataRs.teacherId} AND t1.school_id IN ( ${leadDataRs.schoolIds} ) AND t2.year_class_id IN ( ${leadDataRs.yearClassIds} ) AND t1.subjects_id IN ( ${leadDataRs.subjectsIds} ) ))",
            // ---权限数据结束---

            "<when test='classTypeIdStr!=null and classTypeIdStr!=&apos;&apos; '>",
            "and course_type_id in (${classTypeIdStr})",
            "</when>",
            "<when test='gradeId!=null and gradeId!=&apos;&apos; '>",
            "and t2.id = ${gradeId}",
            "</when>",
            "<when test='startLessonNum!=null and startLessonNum!=&apos;&apos; and endLessonNum!=null and endLessonNum!=&apos;&apos;'>",
            "AND t1.lesson_num BETWEEN ${startLessonNum} AND ${endLessonNum} ",
            "</when>",
            "<when test='isRepair!=null and isRepair!=&apos;&apos; '>",
                "<when test='isRepair == 2'>",
                    " and t1.is_repair = 1",
                    "<when test='lackType == 0'>",
                        "and t1.repair_end_date_time &lt;='2025-05-05 18:00:00' ",
                    "</when>",
                "</when>",
                "<when test='isRepair == 3'>",
                    " and t1.is_repair &lt;&gt; 1",
                "</when>",
                "<when test='isRepair == 4'>",
                " and t1.is_repair = 1 AND t4.school_date BETWEEN '${startDate}' AND '${endDate}' ",
                    "<when test='lackType == 0'>",
                        "and t1.repair_end_date_time &lt;='2025-05-05 18:00:00' ",
                    "</when>",
                "</when>",
            "</when>",
            "<when test='isCollect == 1'>",
            "GROUP BY t1.student_id,t2.teacher_id ",
            "</when>",
            ") AS t02 ON t02.teacher_id = t01.teacher_id ",
            "AND ( find_in_set( t02.year_class_id, t01.year_class_id_str ) OR t01.year_class_id_str = '' ) ",
            "AND ( find_in_set( t02.school_id, t01.school_id_str ) OR t01.school_id_str = '' )",
            "</script>"})
    public IPage<LackRecourseRate> getRepairLackRadeTeamDetail(IPage<?> page, @Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("schoolId") String schoolId, @Param("teacherId") Long teacherId, @Param("gradeId") Long gradeId, @Param("subjectsId") String subjectsId, @Param("startDate") String startDate, @Param("endDate") String endDate, @Param("yearClassId") String yearClassId, @Param("isRepair") String isRepair, @Param("classTypeIdStr") String classTypeIdStr, @Param("teamId") String teamId, @Param("classify") String classify, @Param("trackType") String trackType, @Param("isCollect") Integer isCollect, @Param("lessonNum") String lessonNum, @Param("startLessonNum") String startLessonNum, @Param("endLessonNum") String endLessonNum, @Param("lackType") String lackType, @Param("authAdminContent") AuthAdminContent authAdminContent, @Param("leadDataRs") LeadDataRs leadDataRs);


}
